# xlsxwriter为Python第三方模块,用于向生成的Excel表格
# 插入数据、图表等操作
# 安装: pip install xlsxwriter
# 导入: import xlsxwriter

# xlsxwriter较其他模块支持更多的Exce|功能
# 100%兼容Excel xlsx文件，支持Excel 2003、Excel 2007等版本
# xlsxwriter处理速度更快,支持大文件写入
# coding: utf-8
import xlsxwriter

# 写入文本图片超链接

workbook = xlsxwriter.Workbook("销售信息.xlsx")

# 格式对象
top_format = workbook.add_format({'bold': True})
sub_format = workbook.add_format()
sub_format.set_bold()
sub_format.set_font_color("red")
sub_format.set_font_size(14)
sub_format.set_align("center")

title_format = workbook.add_format()
title_format.set_bold()
title_format.set_bg_color("#808080")
title_format.set_align("center")

cell_format = workbook.add_format()
cell_format.set_font_size(12)
cell_format.set_align("center")

# 创建sheet
sheet = workbook.add_worksheet("销售信息")

# 写入数据
sheet.write(0, 0, "2020年度", top_format)
sheet.merge_range(1, 0, 2, 2, "第一季度销售统计", sub_format)
data = (
    ["一月份", 500, 450],
    ["二月份", 700, 250],
    ["三月份", 600, 350],
)
sheet.write_row(3, 0, ["月份", "预计销售", "实际销售"], title_format)
for index, item in enumerate(data):
    sheet.write_row(index + 4, 0, item, cell_format)


# 写入Excel公式
sheet.write(7, 1, "=sum(B5:B7)", cell_format)
sheet.write(7, 2, "=sum(C5:C7)", cell_format)

# 写入超链接
sheet.write_url(9, 0, "http://www.baidu.com", string="更多信息")

# 写入图片
sheet.insert_image(10, 0, "img.jpg")


# 写入图表
# line 是折线图， column 是直方图
chart = workbook.add_chart({"type": 'column'})
chart.set_title({"name": "第一季度销售统计"})

chart.set_x_axis({"name": "月份"})
chart.set_y_axis({"name": "销售额"})
chart.add_series({
    "name": "预期销售额",
    "categories": "=销售信息!$A$5:$A$7",
    "values": ["销售信息", 4, 1, 6, 1],
    "data_labels": {"value": True}
})
chart.add_series({
    "name": "实际销售额",
    "categories": "=销售信息!$A$5:$A$7",
    "values": ["销售信息", 4, 2, 6, 2],
    "data_labels": {"value": True}
})
sheet.insert_chart("A23", chart)
workbook.close()
